package jdbc;

import java.sql.*;

public class JdbcDemo {

    private static final String SQLITE_DRIVER = "org.sqlite.JDBC";
    private static final String URL = "jdbc:sqlite:01-jdbc-demo/src/main/resources/test.db";
    private static final String USER_NAME = "";
    private static final String PASSWORD = "";


    private static final String SQL_SELECT = "select name from t_user where id=?";

    private static final String SQL_INSERT = "insert into t_user(id,name) values(?,?)";


    public static void main(String[] args) {
        System.out.println(insert(1,"Blues"));
        System.out.println(selectNameById(1));
    }

    private static Connection getConnection(){
        Connection conn = null;
        try {
            Class.forName(SQLITE_DRIVER);
            conn = DriverManager.getConnection(URL,USER_NAME, PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }


    private static Integer insert(Integer id, String name){
        Integer insertNum = null;
        Connection conn = getConnection();
        PreparedStatement statement = null;
        try {
            statement = conn.prepareStatement(SQL_INSERT);
            statement.setInt(1,id);
            statement.setString(2,name);
            insertNum = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                statement.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return insertNum;
    }


    private static String selectNameById(int id){
        String name = null;
        Connection conn = getConnection();
        PreparedStatement statement = null;
        try {
            statement = conn.prepareStatement(SQL_SELECT);
            statement.setInt(1,id);
            ResultSet resultSet =  statement.executeQuery();
            if (resultSet.next()){
                name = resultSet.getString("name");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                statement.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return name;
    }
}
